import os
import pandas as pd
import traceback
from openpyxl import load_workbook
from .sheet_processor import sheet_to_dataframe
from sql.sql_generator import generate_sql
from sql.sql_executor import execute_sql
from config.path_manager import EXCEL_DIR, SQL_DIR
from utils.name_sanitizer import sanitize_name

def find_excel_files(directory):
    """递归查找目录及子目录中的所有Excel文件"""
    excel_files = []
    
    for root, dirs, files in os.walk(directory):
        for file in files:
            if file.endswith((".xlsx", ".xls")):
                file_path = os.path.join(root, file)
                # 计算相对路径用于显示
                rel_path = os.path.relpath(file_path, directory)
                excel_files.append({
                    'name': file,
                    'path': file_path,
                    'relative_path': rel_path,
                    'directory': root
                })
    
    return excel_files

def process_all_excel_files(db_config, batch_size):
    """处理所有Excel文件（包括子目录）"""
    excel_files = find_excel_files(EXCEL_DIR)
    
    if not excel_files:
        print(f"未找到Excel文件，请将Excel文件放入: {EXCEL_DIR}")
        return
    
    print("=" * 80)
    print(f"📊 开始处理 Excel 文件 - 共找到 {len(excel_files)} 个文件")
    print("=" * 80)
    
    # 按目录分组显示文件
    directories = {}
    for file_info in excel_files:
        dir_path = file_info['directory']
        if dir_path not in directories:
            directories[dir_path] = []
        directories[dir_path].append(file_info)
    
    print("📁 发现的Excel文件分布:")
    for dir_path, files in directories.items():
        rel_dir = os.path.relpath(dir_path, EXCEL_DIR)
        if rel_dir == '.':
            print(f"  📂 根目录: {len(files)} 个文件")
        else:
            print(f"  📂 {rel_dir}: {len(files)} 个文件")
        for file_info in files:
            print(f"    📄 {file_info['name']}")
    
    print("\n" + "=" * 60)
    
    for i, file_info in enumerate(excel_files, 1):
        file_path = file_info['path']
        file_name = file_info['name']
        rel_path = file_info['relative_path']
        
        print(f"\n{'='*60}")
        print(f"📁 [{i}/{len(excel_files)}] 处理文件: {rel_path}")
        print(f"📍 完整路径: {file_path}")
        print(f"{'='*60}")
        
        try:
            process_excel_file(file_path, db_config, batch_size)
            print(f"✅ 文件 {rel_path} 处理完成")
        except Exception as e:
            print(f"❌ 处理 {rel_path} 时出错: {str(e)}")
            print(f"详细错误信息:")
            print(traceback.format_exc())
            print("-" * 50)
    
    print(f"\n{'='*80}")
    print(f"🎉 所有 Excel 文件处理完成！")
    print(f"{'='*80}")

def process_excel_file(file_path, db_config, batch_size):
    """处理单个Excel文件"""
    try:
        wb = load_workbook(file_path, data_only=True)
    except Exception as e:
        print(f"无法加载Excel文件 {file_path}: {str(e)}")
        return
        
    # 使用相对路径作为文件名前缀，避免路径冲突
    rel_path = os.path.relpath(file_path, EXCEL_DIR)
    file_name = os.path.splitext(rel_path)[0].replace(os.sep, '_')  # 将路径分隔符替换为下划线
    
    total_sheets = len(wb.sheetnames)
    
    print(f"📋 发现 {total_sheets} 个工作表: {', '.join(wb.sheetnames)}")
    
    for i, sheet_name in enumerate(wb.sheetnames, 1):
        print(f"\n{'-'*40}")
        print(f"📄 [{i}/{total_sheets}] 处理工作表: {sheet_name}")
        print(f"{'-'*40}")
        
        ws = wb[sheet_name]
        
        # 转换为DataFrame
        df = sheet_to_dataframe(ws)
        
        # 跳过空表
        if df.empty:
            print(f"⚠️  工作表 {sheet_name} 为空，跳过")
            continue
        
        print(f"📊 数据维度: {df.shape[0]} 行 x {df.shape[1]} 列")
            
        # 生成表名（包含路径信息避免冲突）
        table_name = f"{file_name}_{sheet_name}"
        
        # 生成SQL
        create_sql, insert_sql_batches = generate_sql(table_name, df, batch_size)
        
        # 保存SQL文件
        save_sql_file(table_name, create_sql, insert_sql_batches)
        
        # 执行SQL
        execute_sql(db_config, create_sql, insert_sql_batches)
        
        print(f"✅ 工作表 {sheet_name} 处理完成")

def save_sql_file(table_name, create_sql, insert_sql_batches):
    """保存SQL到文件"""
    from config.path_manager import SQL_DIR
    from utils.name_sanitizer import sanitize_name
    
    sql_file_name = f"{sanitize_name(table_name)}.sql"
    sql_file_path = os.path.join(SQL_DIR, sql_file_name)
    
    with open(sql_file_path, "w", encoding="utf-8") as f:
        f.write(f"-- 建表语句\n{create_sql}\n\n")
        f.write(f"-- 插入数据 ({len(insert_sql_batches)} 批次)\n")
        for i, insert_sql in enumerate(insert_sql_batches, 1):
            f.write(f"-- 批次 #{i}\n")
            f.write(f"{insert_sql}\n\n")
    
    print(f"💾 已生成SQL文件: {sql_file_name}")